import pandas as pd

# 假设的JSON数据
json_data = {
    "name": "张三",
    "age": 30,
    "address": {
        "city": "北京",
        "street": "朝阳路"
    }
}

# 将JSON数据扁平化并创建DataFrame
def flatten_json(y, parent_key='', sep='_'):
    items = []
    for k, v in y.items():
        new_key = parent_key + sep + k if parent_key else k
        if isinstance(v, dict):
            items.extend(flatten_json(v, new_key, sep=sep).items())
        else:
            items.append((new_key, v))
    return dict(items)

# 扁平化JSON数据
flattened_data = flatten_json(json_data)

# 提取层级目录和父元素key
def extract_hierarchy(key):
    parts = key.split('_')
    level = len(parts) - 1
    parent_key = '_'.join(parts[:-1]) if len(parts) > 1 else None
    return level, parent_key

# 创建DataFrame
df_data = []
for key, value in flattened_data.items():
    level, parent_key = extract_hierarchy(key)
    element_key = key.split('_')[-1]
    data_type = type(value).__name__
    df_data.append([level, parent_key, element_key, data_type])

# 创建DataFrame
df = pd.DataFrame(df_data, columns=['层级目录', '父元素key', '元素key', '数据类型'])

# 写入Excel文件
df.to_excel('request_specification.xlsx', index=False, engine='openpyxl')

print("Excel文件已生成。")